package dao.impl;

import dao.GoodsDao;
import model.bo.SpecBO;
import model.po.Goods;
import model.po.Message;
import model.po.Spec;
import model.po.Type;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.DruidUtils;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class GoodsDaoImpl implements GoodsDao {
    /**
     * 1.从数据库中查询所有的Type并封装到一个List中
     * @return 商城中所有的商品Type
     */
    @Override
    public List<Type> getAllTypes() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from `type`";
        List<Type> typeList = null;
        try {
            typeList = queryRunner.query(sql, new BeanListHandler<>(Type.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return typeList;
    }

    @Override
    public List<Goods> getGoodsByType(Type type) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from `dgoods` where `type_id` = ?";
        List<Goods> goodList = null;
        Map<String, String> fieldsMapper = new HashMap<>();
        fieldsMapper.put("type_id", "typeId");
        try {
            goodList = queryRunner.query(
                    sql,
                    new BeanListHandler<>(Goods.class, new BasicRowProcessor(new BeanProcessor(fieldsMapper))),
                    type.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return goodList;
    }

    @Override
    public int addGoods(Goods goods) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "insert into `dgoods` values(null, ?, ?, ?, ?, ?)";
        int result = 0;
        try {
            result = queryRunner.update(sql, goods.getName(), goods.getTypeId(), goods.getImg(), goods.getDesc(), goods.getPrice());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public int lastInsertId() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select last_insert_id() from `dgoods`";
        BigInteger result = null;
        try {
            result = queryRunner.query(sql, new ScalarHandler<>());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null == result ? 0 : result.intValue();
    }

    @Override
    public int addSpecs(List<Spec> specs) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "insert into `dspec` values(null, ?, ?, ?, ?)";
        Object[][] batchParams = new Object[specs.size()][4];
        for (int i = 0; i < batchParams.length; i++) {
            batchParams[i] = new Object[] {
                    specs.get(i).getGoodsId(),
                    specs.get(i).getName(),
                    specs.get(i).getNumber(),
                    specs.get(i).getPrice()
            };
        }
        int result = 0;
        try {
            int[] rows = queryRunner.batch(sql, batchParams);
            for (int r : rows) { result += r; }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public Goods getGoodsInfo(Integer id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from `dgoods` where `id` = ?";
        Goods goods = null;
        Map<String, String> map = new HashMap<>();
        map.put("type_id", "typeId");
        try {
            goods = queryRunner.query(sql, new BeanHandler<>(Goods.class, new BasicRowProcessor(new BeanProcessor(map))), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return goods;
    }

    @Override
    public List<Spec> getSpecs(Integer goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from `dspec` where `goods_id` = ?";
        List<Spec> specs = null;
        Map<String, String> map = new HashMap<>();
        map.put("goods_id", "goodsId");
        try {
            specs = queryRunner.query(sql, new BeanListHandler<>(Spec.class, new BasicRowProcessor(new BeanProcessor(map))), goodsId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return specs;
    }

    @Override
    public int deleteSpec(SpecBO spec) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "delete from `dspec` where `goods_id` = ? and `name` = ?";
        int result = 0;
        try {
            result = queryRunner.update(sql, spec.getGoodsId(), spec.getSpecName());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public int updateGoods(Goods goods) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "update `dgoods` set `name` = ?, `type_id` = ?, `img` = ?, `desc` = ?, `price` = ? where `id` = ?";
        int result = 0;
        try {
            result = queryRunner.update(sql, goods.getName(), goods.getTypeId(), goods.getImg(), goods.getDesc(), goods.getPrice(), goods.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public int updateSpecs(List<Spec> specList) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "update `dspec` set `name` = ?, `number` = ?, `price` = ? where `id` = ?";
        Object[][] batchParams = new Object[specList.size()][4];
        for (int i = 0; i < batchParams.length; i++) {
            batchParams[i] = new Object[] {
                    specList.get(i).getName(),
                    specList.get(i).getNumber(),
                    specList.get(i).getPrice(),
                    specList.get(i).getId()
            };
        }
        int result = 0;
        try {
            int[] rows = queryRunner.batch(sql, batchParams);
            for (int r : rows) { result += r; }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public int deleteGoods(Integer id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sqlSpec = "delete from `dspec` where `goods_id` = ?";
        String sqlGoods = "delete from `dgoods` where `id` = ?";
        int result = 0;
        try {
            queryRunner.update(sqlSpec, id);
            result = queryRunner.update(sqlGoods, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public List<String> getGoodsNameByIds(List<Integer> ids) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select `name` from `dgoods` where `id` = ?";
        List<String> nameList = new ArrayList<>(ids.size());
        try {
            for (Integer id : ids) {
                nameList.add(
                        queryRunner
                                .query(
                                        sql,
                                        resultSet -> resultSet.next() ? resultSet.getString(1) : null,
                                        id));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return nameList;
    }

    @Override
    public List<Message> repliedMsg() {
        return getMessages(0);
    }

    @Override
    public List<Message> noReplyMsg() {
        return getMessages(1);
    }

    private List<Message> getMessages(int state) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from `message` where `state` = ?";
        List<Message> messages = null;
        Map<String, String> map = new HashMap<>();
        map.put("user_id", "userId");
        map.put("goods_id", "goodsId");
        map.put("reply_content", "replyContent");
        try {
            messages = queryRunner.query(sql, new BeanListHandler<>(Message.class, new BasicRowProcessor(new BeanProcessor(map))), state);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return messages;
    }

    @Override
    public int reply(Message msg) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "update `message` set `reply_content` = ?, `state` = ? where `id` = ?";
        int result = 0;
        try {
            result = queryRunner.update(sql, msg.getReplyContent(), msg.getState(), msg.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public int setPrice(Integer id, Double price) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "update `dgoods` set `price` = ? where `id` = ?";
        int result = 0;
        try {
            result = queryRunner.update(sql, price, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }
}
